package com.spring.common.util;


/**
 * @Copyright: Shanghai Definesys Company.All rights reserved.
 * @Description: 导出的sql
 * @author: chuhaitao
 * @since: 2019/3/6 14:18
 * @history: 1.2019/3/6 created by chuhaitao
 */
public class ExportSql {


    /**
     * 资金资信的导出sql
     */
    public static String creditCretExportSql = " select * from (SELECT tmp1.*, tmp4.typeName as CERT_TYPE_NAME FROM (SELECT fh.CREDIT_CERT_HEADER_ID, fh.DOCUMENT_NUMBER, fh.ORG_ID, (SELECT fo.ORGANIZATION_NAME FROM fis.FIS_ORGANIZATIONS_T fo WHERE fo.ORGANIZATION_ID = fh.ORG_ID AND ROWNUM = 1) AS ORG_NAME, fh.APPROVE_DEPT_CODE, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.APPROVE_DEPT_CODE AND lv.LOOKUP_TYPE = 'FIS_APPROVE_DEPT_TYPE' AND ROWNUM = 1)) AS APPROVE_DEPT_NAME, fh.BENEFICIARY, fh.APPLY_DATE, fh.BID_PROJECT_NAME, fh.BID_FLAG, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.BID_FLAG AND lv.LOOKUP_TYPE = 'FIS_BID_TYPE' AND ROWNUM = 1)) AS BID_FLAG_NAME, fh.STATUS_CODE, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.STATUS_CODE AND lv.LOOKUP_TYPE = 'FIS_DOCUMENT_STATUS' AND ROWNUM = 1)) AS STATUS_NAME, fh.RETURN_FLAG, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.RETURN_FLAG AND lv.LOOKUP_TYPE = 'FIS_RETURN_TYPE' AND ROWNUM = 1)) AS RETURN_FLAG_NAME, fh.CERT_TYPE, fh.OBJECT_VERSION_NUMBER, fh.CREATION_DATE, fh.CREATED_BY, (SELECT U.EMPLOYEE_NAME FROM FIS.FIS_USERS_T U WHERE to_char(U.USER_ID) = fh.CREATED_BY) AS CREATED_BY_NAME, fh.LAST_UPDATED_BY, fh.LAST_UPDATE_DATE, fh.LAST_UPDATE_LOGIN, fh.ATTRIBUTE_CATEGORY, fh.oversea_flag FROM fis.FIS_CREDIT_CERT_HEADER_T fh where fh.created_by = #userId union all SELECT fh.CREDIT_CERT_HEADER_ID, fh.DOCUMENT_NUMBER, fh.ORG_ID, (SELECT fo.ORGANIZATION_NAME FROM fis.FIS_ORGANIZATIONS_T fo WHERE fo.ORGANIZATION_ID = fh.ORG_ID AND ROWNUM = 1) AS ORG_NAME, fh.APPROVE_DEPT_CODE, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.APPROVE_DEPT_CODE AND lv.LOOKUP_TYPE = 'FIS_APPROVE_DEPT_TYPE' AND ROWNUM = 1)) AS APPROVE_DEPT_NAME, fh.BENEFICIARY, fh.APPLY_DATE, fh.BID_PROJECT_NAME, fh.BID_FLAG, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.BID_FLAG AND lv.LOOKUP_TYPE = 'FIS_BID_TYPE' AND ROWNUM = 1)) AS BID_FLAG_NAME, fh.STATUS_CODE, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.STATUS_CODE AND lv.LOOKUP_TYPE = 'FIS_DOCUMENT_STATUS' AND ROWNUM = 1)) AS STATUS_NAME, fh.RETURN_FLAG, (SELECT lv.MEANING FROM fis.FIS_LOOKUP_VALUES_T lv WHERE (lv.LOOKUP_CODE = fh.RETURN_FLAG AND lv.LOOKUP_TYPE = 'FIS_RETURN_TYPE' AND ROWNUM = 1)) AS RETURN_FLAG_NAME, fh.CERT_TYPE, fh.OBJECT_VERSION_NUMBER, fh.CREATION_DATE, fh.CREATED_BY, (SELECT U.EMPLOYEE_NAME FROM FIS.FIS_USERS_T U WHERE to_char(U.USER_ID) = fh.CREATED_BY) AS CREATED_BY_NAME, fh.LAST_UPDATED_BY, fh.LAST_UPDATE_DATE, fh.LAST_UPDATE_LOGIN, fh.ATTRIBUTE_CATEGORY, fh.oversea_flag FROM fis.FIS_CREDIT_CERT_HEADER_T fh where fh.created_by != #userId and fh.status_code != 'NEW') tmp1, (select LISTAGG(typeName) WITHIN GROUP(order BY typeName) AS typeName, tmp3.credit_cert_header_id from (select tmp2.cretType, tmp2. credit_cert_header_id, lv.meaning || ';' as typeName from (SELECT DISTINCT REGEXP_SUBSTR(ch.cert_type, '[^;]+', 1, LEVEL) as cretType, ch.credit_cert_header_id from fis.FIS_CREDIT_CERT_HEADER_T ch connect by REGEXP_SUBSTR(ch.cert_type, '[^;]+', 2, LEVEL) IS NOT NULL) tmp2, fis.fis_lookup_values_t lv where tmp2.cretType = lv.lookup_code(+) and lv.lookup_type = 'FIS_CREDIT_CERT_TYPE') tmp3 group by credit_cert_header_id) tmp4 WHERE tmp1.CREDIT_CERT_HEADER_ID = tmp4.credit_cert_header_id and tmp1.org_id IN (SELECT ORGANIZATION_ID AS organizationId FROM FIS_ORGANIZATIONS_T WHERE ORGANIZATION_TYPE = 'CCEED5' START WITH ORGANIZATION_ID IN (SELECT DISTINCT DECODE(OT.ORGANIZATION_TYPE, 'CCEED5', OT.ORGANIZATION_ID, OT.PARENT_ORGANIZATION) FROM FIS_ORG_USERS_T UT, FIS_ORGANIZATIONS_T OT WHERE UT.ORG_ID = OT.ORGANIZATION_ID AND UT.ENABLED_FLAG = 'Enabled' AND UT.USER_ID = #userId) CONNECT BY PRIOR ORGANIZATION_ID = Parent_Organization)) tmp5  ";

    /**
     * 融资招标的导出sql
     */
    public static String tenderExportSql = "select tmp.* from (SELECT * FROM (SELECT t1.TENDER_ID, t1.DOCUMENT_NUMBER, t1.TENDER_TYPE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.TENDER_TYPE AND v.LOOKUP_TYPE = 'FIS_BUSINESS_TYPE' AND ROWNUM = 1)) AS TENDER_TYPE_NAME, t1.APPLY_ORG_ID, (SELECT org.ORGANIZATION_NAME FROM FIS.FIS_ORGANIZATIONS_T org WHERE org.ORGANIZATION_ID = t1.APPLY_ORG_ID) AS APPLY_ORG_NAME, t1.USE_ORG_ID, (SELECT org.ORGANIZATION_NAME FROM FIS.fis_ORGANIZATIONS_T org WHERE org.ORGANIZATION_ID = t1.USE_ORG_ID) AS USE_ORG_NAME, t1.APPLY_DATE, t1.FINANCE_AMOUNT / 10000 as FINANCE_AMOUNT, t1.CURRENCY_CODE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (V.LOOKUP_CODE = t1.CURRENCY_CODE AND v.LOOKUP_TYPE = 'FIS_CURRENCY_TYPE' AND ROWNUM = 1)) AS CURRENCY_NAME, t1.EXCHANGE_RATE, t1.CONVERT_AMOUNT, (SELECT pv.PROJECT_NAME FROM FIS_FIN_TENDER_SETS_T t, fis_projects_v pv WHERE (t.value_id = pv.PROJECT_ID AND t.set_code = 'TENDER' AND t.tender_id = t1.tender_id AND rownum = 1)) AS PROJECT_NAME, t1.FINANCE_DEADLINE, t1.PREDICTED_COST, t1.TENDER_DESC, t1.STATUS_CODE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.STATUS_CODE AND v.LOOKUP_TYPE = 'FIS_DOCUMENT_STATUS' AND ROWNUM = 1)) AS STATUS_NAME, t1.TENDER_STAGE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.TENDER_STAGE AND v.LOOKUP_TYPE = 'FIS_FIN_TENDER_STAGE' AND ROWNUM = 1)) AS TENDER_STAGE_NAME, T1.WINNING_BIDDER_IDS, t1.BID_PRICE, t1.CREATED_BY, (SELECT u.EMPLOYEE_NAME FROM fis.FIS_USERS_T u WHERE to_char(u.USER_ID) = t1.CREATED_BY) AS CREATED_BY_NAME, LAST_UPDATE_DATE, t1.Oversea_Flag FROM fis.FIS_FIN_TENDERS_T t1 where t1.created_by = #userId union all SELECT t1.TENDER_ID, t1.DOCUMENT_NUMBER, t1.TENDER_TYPE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.TENDER_TYPE AND v.LOOKUP_TYPE = 'FIS_BUSINESS_TYPE' AND ROWNUM = 1)) AS TENDER_TYPE_NAME, t1.APPLY_ORG_ID, (SELECT org.ORGANIZATION_NAME FROM FIS.FIS_ORGANIZATIONS_T org WHERE org.ORGANIZATION_ID = t1.APPLY_ORG_ID) AS APPLY_ORG_NAME, t1.USE_ORG_ID, (SELECT org.ORGANIZATION_NAME FROM FIS.fis_ORGANIZATIONS_T org WHERE org.ORGANIZATION_ID = t1.USE_ORG_ID) AS USE_ORG_NAME, t1.APPLY_DATE, t1.FINANCE_AMOUNT / 10000 as FINANCE_AMOUNT, t1.CURRENCY_CODE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (V.LOOKUP_CODE = t1.CURRENCY_CODE AND v.LOOKUP_TYPE = 'FIS_CURRENCY_TYPE' AND ROWNUM = 1)) AS CURRENCY_NAME, t1.EXCHANGE_RATE, t1.CONVERT_AMOUNT, (SELECT pv.PROJECT_NAME FROM FIS_FIN_TENDER_SETS_T t, fis_projects_v pv WHERE (t.value_id = pv.PROJECT_ID AND t.set_code = 'TENDER' AND t.tender_id = t1.tender_id AND rownum = 1)) AS PROJECT_NAME, t1.FINANCE_DEADLINE, t1.PREDICTED_COST, t1.TENDER_DESC, t1.STATUS_CODE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.STATUS_CODE AND v.LOOKUP_TYPE = 'FIS_DOCUMENT_STATUS' AND ROWNUM = 1)) AS STATUS_NAME, t1.TENDER_STAGE, (SELECT v.MEANING FROM fis.FIS_LOOKUP_VALUES_T v WHERE (v.LOOKUP_CODE = t1.TENDER_STAGE AND v.LOOKUP_TYPE = 'FIS_FIN_TENDER_STAGE' AND ROWNUM = 1)) AS TENDER_STAGE_NAME, T1.WINNING_BIDDER_IDS, t1.BID_PRICE, t1.CREATED_BY, (SELECT u.EMPLOYEE_NAME FROM fis.FIS_USERS_T u WHERE to_char(u.USER_ID) = t1.CREATED_BY) AS CREATED_BY_NAME, LAST_UPDATE_DATE, T1.OVERSEA_FLAG FROM fis.FIS_FIN_TENDERS_T t1 where t1.created_by != #userId and t1.status_code != 'NEW') TMP WHERE TMP.apply_org_id IN (SELECT ORGANIZATION_ID AS organizationId FROM FIS_ORGANIZATIONS_T WHERE ORGANIZATION_TYPE = 'CCEED5' START WITH ORGANIZATION_ID IN (SELECT DISTINCT DECODE(OT.ORGANIZATION_TYPE, 'CCEED5', OT.ORGANIZATION_ID, OT.PARENT_ORGANIZATION) FROM FIS_ORG_USERS_T UT, FIS_ORGANIZATIONS_T OT WHERE UT.ORG_ID = OT.ORGANIZATION_ID AND UT.ENABLED_FLAG = 'Enabled' AND UT.USER_ID = #userId) CONNECT BY PRIOR ORGANIZATION_ID = Parent_Organization)) tmp ";

    /**
     * 保理导出sql
     */
    public static String blExportSql = "SELECT * FROM (SELECT b.bl_apply_id, b.launch_or_create_flag, b.document_number, b.org_id, (SELECT o.organization_name FROM fis_organizations_t o WHERE o.organization_id = b.org_id) AS org_name, b.apply_date, b.tender_id, (SELECT ft.winning_bidder_ids FROM fis_fin_tenders_t ft WHERE ft.tender_id = b.tender_id) AS winning_bidder_ids, (select rtrim(tmp.winning_bidder_names, ';') from (select LISTAGG(winning_bidder_name) WITHIN GROUP(order BY winning_bidder_name) AS winning_bidder_names, tmp3.tender_id from (select tmp2.winning_bidder_id, tmp2. tender_id, i.institution_name || ';' as winning_bidder_name from (SELECT DISTINCT REGEXP_SUBSTR(ft.winning_bidder_ids, '[^;]+', 1, LEVEL) as winning_bidder_id, ft.tender_id from fis_fin_tenders_t ft connect by REGEXP_SUBSTR(ft.winning_bidder_ids, '[^;]+', 2, LEVEL) IS NOT NULL) tmp2, fis_institutions_t i where tmp2. winning_bidder_id = i.institution_id) tmp3 group by tender_id) tmp where tmp.tender_id = b.tender_id) as winning_bidders, b.recourse_flag, (SELECT lv.meaning FROM fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_BL_YES_NO' AND lv.lookup_code = b.recourse_flag) AS recourse_meaning, cast(round(b.transfer_amt / 10000, 2) as numeric(38, 2)) as transfer_amt, cast(round((SELECT SUM(bar.capital_amt) FROM fis_bl_acct_rec_t bar WHERE bar.bl_apply_id = b.bl_apply_id) / 10000, 2) as numeric(38, 2)) AS capital_amt, b.status_code, (SELECT lv.meaning FROM fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_DOCUMENT_STATUS' AND lv.lookup_code = b.status_code) AS status_meaning, (select rtrim(tmp.cntrAmt, ';') from (select LISTAGG(cntrAmt) WITHIN GROUP(order BY cntrAmt) AS cntrAmt, tmp3.bl_apply_id from (select cast(round(bc.cntr_amt / 10000, 2) as numeric(38, 2)) || ';' as cntrAmt, bc.bl_apply_id from fis_bl_cntr_t bc) tmp3 group by bl_apply_id) tmp where tmp.bl_apply_id = b.bl_apply_id) as cntr_amt, (select rtrim(tmp.annual_capital_cost_rate, ';') from (select LISTAGG(annual_capital_cost_rate) WITHIN GROUP(order BY annual_capital_cost_rate) AS annual_capital_cost_rate, tmp3.bl_apply_id from (select decode(bc.annual_capital_cost_rate * 100, null, '', decode(bc.annual_capital_cost_rate * 100, 0, '0.00', trim(to_char(bc.annual_capital_cost_rate * 100, 'fm999999999999990.00'))) || '%;') as annual_capital_cost_rate, bc.bl_apply_id from fis_bl_cntr_t bc) tmp3 group by bl_apply_id) tmp where tmp.bl_apply_id = b.bl_apply_id) as annual_capital_cost_rate, b.oversea_flag FROM fis_bl_apply_t b WHERE (b.org_id IN (SELECT OT.ORGANIZATION_ID AS organizationId FROM FIS_ORGANIZATIONS_T OT START WITH ORGANIZATION_ID IN (SELECT DECODE(OT.ORGANIZATION_TYPE, 'CCEED5', OT.ORGANIZATION_ID, OT.PARENT_ORGANIZATION) FROM FIS_ORG_USERS_T UT, FIS_ORGANIZATIONS_T OT WHERE (UT.ORG_ID = OT.ORGANIZATION_ID AND UT.ENABLED_FLAG = 'Enabled' AND UT.USER_ID = #uid)) CONNECT BY PRIOR ORGANIZATION_ID = Parent_Organization) AND B.STATUS_CODE != 'NEW') OR (B.CREATED_BY = #uid AND B.STATUS_CODE = 'NEW')) ";

    /**
     * 预算导出
     */


    public static String budgetExportSql = " SELECT fa.apply_date, fa.budget_adjust_apply_id, fa.document_number, fb.BUDGET_AMT AS EXIT_AMT, fb.USED_AMT,(SELECT fl.budget_ctrl_name FROM fis.fis_budget_line_t fl WHERE fl.budget_line_id = fa.budget_line_id) AS BUDGET_CTRL_NAME, fa.org_id, (SELECT org.organization_name FROM fis.fis_organizations_t org WHERE org.organization_id = fa.org_id) AS ORG_NAME, fa.status_code, (SELECT lv.meaning FROM fis.fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_DOCUMENT_STATUS' AND lv.lookup_code = fa.status_code) AS status_name, fa.budget_line_id, fa.budget_type, (SELECT lv.meaning FROM fis.fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_BG_BUDGET_CATEGORY_TYPE' AND lv.lookup_code = fa.budget_type) AS BUDGET_TYPE_NAME, fa.available_amt, fa.apply_amt, fa.due_date, fa.apply_reason, fa.created_by, (SELECT U.EMPLOYEE_NAME FROM FIS.FIS_USERS_T U WHERE to_char(U.USER_ID) = fa.created_by) AS CREATED_BY_NAME FROM FIS_BUDGET_ADJUST_APPLY_T fa, fis.fis_budget_line_t fb WHERE fb.budget_line_id(+) = fa.budget_line_id and fa.org_id in ( SELECT ORGANIZATION_ID AS organizationId FROM FIS_ORGANIZATIONS_T WHERE ORGANIZATION_TYPE = 'CCEED5' START WITH ORGANIZATION_ID IN (SELECT DISTINCT DECODE(OT.ORGANIZATION_TYPE, 'CCEED5', OT.ORGANIZATION_ID, OT.PARENT_ORGANIZATION) FROM FIS_ORG_USERS_T UT, FIS_ORGANIZATIONS_T OT WHERE UT.ORG_ID = OT.ORGANIZATION_ID AND UT.ENABLED_FLAG = 'Enabled' AND UT.USER_ID =#userId) CONNECT BY PRIOR ORGANIZATION_ID = Parent_Organization)  ";

    /**
     * 资产证券化导出
     */

    public static String absExportSql = " select * from (select (tmp5.transfer_amt - tmp5.access_amt) as TOTAL_COST, tmp5.*, tmp4. institutionName as WINNING_BIDDER_NAME from (SELECT t.tender_id, t.apply_deadline, (SELECT tt.document_number FROM fis_fin_tenders_t tt WHERE tt.tender_id = t.tender_id) tender_docnumber, t.org_id, (SELECT ot.organization_name FROM fis_organizations_t ot WHERE ot.organization_id = t.org_id) AS org_name, (SELECT ft.winning_bidder_ids FROM fis_fin_tenders_t ft WHERE ft.tender_id = t.tender_id) winning_bidder_ids, t.abs_apply_id, t.document_number, t.apply_date, t.status_code, (SELECT lv.meaning FROM fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_DOCUMENT_STATUS' AND lv.lookup_code = t.status_code) AS status_name, t.product_name, t.base_asset_code, (SELECT lv.meaning FROM fis_lookup_values_t lv WHERE lv.lookup_type = 'FIS_ABS_BASE_ASSET_TYPE' AND lv.lookup_code = t.base_asset_code) AS base_asset_name, t.transfer_period, t.currency_code, t.product_managers, t.beneficiary_org_id, t.underwrite_inst_ids, t.interest_payment_method, t.first_ratio, t.second_ratio, t.LAUNCH_OR_CREATE_FLAG, t.biz_plan_desc, t.stock_approval_number, (SELECT ot.organization_name FROM fis_organizations_t ot WHERE ot.organization_id = t.beneficiary_org_id) beneficiary_org_name, t.cscec_submit_number, (SELECT decode(SUM(ap.in_rec_amt), null, 0.0, (SUM(ap.in_rec_amt))) / 10000 FROM fis_abs_acct_rec_t ap, fis_abs_cntr_t ac WHERE ap.abs_cntr_id = ac.abs_cntr_id and ac.abs_apply_id = t.abs_apply_id) as transfer_amt, (SELECT decode(SUM(ap.capital_amt), null, 0.0, (SUM(ap.capital_amt)) / 10000) FROM fis_abs_acct_rec_t ap, fis_abs_cntr_t ac WHERE ap.abs_cntr_id = ac.abs_cntr_id and ac.abs_apply_id = t.abs_apply_id) AS access_amt, t.created_by, (SELECT u.employee_name FROM fis_users_t u WHERE u.user_id = t.created_by) AS created_by_name, t.oversea_flag FROM fis_abs_apply_t t where t.ORG_ID in (SELECT ORGANIZATION_ID AS organizationId FROM FIS_ORGANIZATIONS_T WHERE ORGANIZATION_TYPE = 'CCEED5' START WITH ORGANIZATION_ID IN (SELECT DISTINCT DECODE(OT.ORGANIZATION_TYPE, 'CCEED5', OT.ORGANIZATION_ID, OT.PARENT_ORGANIZATION) FROM FIS_ORG_USERS_T UT, FIS_ORGANIZATIONS_T OT WHERE UT.ORG_ID = OT.ORGANIZATION_ID AND UT.ENABLED_FLAG = 'Enabled' AND UT.USER_ID = #userId) CONNECT BY PRIOR ORGANIZATION_ID = Parent_Organization)) tmp5, (select LISTAGG(institutionName) WITHIN GROUP(order BY institutionName) AS institutionName, tmp3.tender_id from (select tmp2.bidId, tmp2. tender_id, decode(fi.institution_name, null, null, fi.institution_name || ';') as institutionName from (SELECT DISTINCT REGEXP_SUBSTR(ft.winning_bidder_ids, '[^;]+', 1, LEVEL) as bidId, ft.tender_id from fis. FIS_FIN_TENDERS_T ft connect by REGEXP_SUBSTR(ft.winning_bidder_ids, '[^;]+', 2, LEVEL) IS NOT NULL) tmp2, fis.fis_institutions_t fi where tmp2.bidId = fi.institution_id(+)) tmp3 group by tender_id) tmp4 where tmp5.tender_id = tmp4.tender_id) tmp6 ";

}